import os
import sqlite3
import shutil
import json
from datetime import datetime

# 数据库配置
DATABASE = 'todos.db'
BACKUP_DIR = 'db_backups'

def backup_database():
    """备份现有数据库"""
    if not os.path.exists(DATABASE):
        print(f"数据库文件 {DATABASE} 不存在，无需备份")
        return False
    
    # 创建备份目录
    if not os.path.exists(BACKUP_DIR):
        os.makedirs(BACKUP_DIR)
    
    # 生成备份文件名，包含时间戳
    timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
    backup_file = os.path.join(BACKUP_DIR, f"todos_backup_{timestamp}.db")
    
    # 复制数据库文件
    shutil.copy2(DATABASE, backup_file)
    print(f"数据库已备份到: {backup_file}")
    return backup_file

def extract_data(backup_file):
    """从备份数据库中提取数据"""
    data = {'users': [], 'todos': []}
    
    try:
        conn = sqlite3.connect(backup_file)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        
        # 检查users表是否存在
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='users'")
        if cursor.fetchone():
            cursor.execute("SELECT * FROM users")
            data['users'] = [dict(row) for row in cursor.fetchall()]
            print(f"从备份中提取了 {len(data['users'])} 个用户")
        
        # 检查todos表是否存在
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='todos'")
        if cursor.fetchone():
            cursor.execute("SELECT * FROM todos")
            data['todos'] = [dict(row) for row in cursor.fetchall()]
            print(f"从备份中提取了 {len(data['todos'])} 个待办事项")
        
        conn.close()
    except Exception as e:
        print(f"提取数据时出错: {e}")
    
    return data

def create_new_database():
    """创建新的数据库结构"""
    # 如果数据库文件存在，先删除
    if os.path.exists(DATABASE):
        os.remove(DATABASE)
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 创建用户表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        password TEXT NOT NULL,
        email TEXT UNIQUE,
        created_at TIMESTAMP NOT NULL,
        last_login TIMESTAMP,
        token TEXT,
        token_expiry TIMESTAMP
    )
    ''')
    
    # 创建待办事项表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS todos (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        title TEXT NOT NULL,
        description TEXT,
        priority INTEGER NOT NULL DEFAULT 2,
        due_date TIMESTAMP,
        completed BOOLEAN NOT NULL DEFAULT 0,
        created_at TIMESTAMP NOT NULL,
        updated_at TIMESTAMP NOT NULL,
        FOREIGN KEY (user_id) REFERENCES users (id)
    )
    ''')
    
    conn.commit()
    conn.close()
    print("已创建新的数据库结构")

def migrate_data(data):
    """将数据迁移到新数据库"""
    if not data['users'] and not data['todos']:
        print("没有数据需要迁移")
        return
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 迁移用户数据
    for user in data['users']:
        # 准备字段和值
        fields = []
        values = []
        placeholders = []
        
        for key, value in user.items():
            if key != 'id':  # 跳过id字段，让它自动递增
                fields.append(key)
                values.append(value)
                placeholders.append('?')
        
        # 构建INSERT语句
        sql = f"INSERT INTO users ({', '.join(fields)}) VALUES ({', '.join(placeholders)})"
        
        try:
            cursor.execute(sql, values)
            # 如果需要保持相同的ID，可以使用以下语句
            if 'id' in user:
                cursor.execute(f"UPDATE SQLITE_SEQUENCE SET seq = {max(user['id'], cursor.lastrowid)} WHERE name = 'users'")
        except sqlite3.IntegrityError as e:
            print(f"迁移用户 {user.get('username')} 时出错: {e}")
    
    # 迁移待办事项数据
    for todo in data['todos']:
        # 准备字段和值
        fields = []
        values = []
        placeholders = []
        
        for key, value in todo.items():
            if key != 'id':  # 跳过id字段，让它自动递增
                fields.append(key)
                values.append(value)
                placeholders.append('?')
        
        # 构建INSERT语句
        sql = f"INSERT INTO todos ({', '.join(fields)}) VALUES ({', '.join(placeholders)})"
        
        try:
            cursor.execute(sql, values)
            # 如果需要保持相同的ID，可以使用以下语句
            if 'id' in todo:
                cursor.execute(f"UPDATE SQLITE_SEQUENCE SET seq = {max(todo['id'], cursor.lastrowid)} WHERE name = 'todos'")
        except sqlite3.IntegrityError as e:
            print(f"迁移待办事项 {todo.get('title')} 时出错: {e}")
    
    conn.commit()
    conn.close()
    print(f"已迁移 {len(data['users'])} 个用户和 {len(data['todos'])} 个待办事项")

def main():
    print("===== 数据库重建工具 =====")
    
    # 备份现有数据库
    backup_file = backup_database()
    
    if backup_file:
        # 提取数据
        data = extract_data(backup_file)
        
        # 创建新数据库
        create_new_database()
        
        # 迁移数据
        migrate_data(data)
        
        print("数据库重建完成！")
    else:
        # 如果没有现有数据库，直接创建新的
        create_new_database()
        print("已创建新的空数据库")

if __name__ == "__main__":
    main()
